/**
 *    This program is free software; you can redistribute it and/or modify
 *    it under the terms of the GNU General Public License as published by
 *    the Free Software Foundation; either version 2 of the License, or
 *    (at your option) any later version.
 *
 *    This program is distributed in the hope that it will be useful,
 *    but WITHOUT ANY WARRANTY; without even the implied warranty of
 *    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *    GNU General Public License for more details.
 *
 *    You should have received a copy of the GNU General Public License
 *    along with this program; if not, write to the Free Software
 *    Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.
 *    
 *    Linking this library statically or dynamically with other modules 
 *    is making a combined work based on this library. Thus, the terms and
 *    conditions of the GNU General Public License cover the whole
 *    combination.
 *    
 *    As a special exception, the copyright holders of this library give 
 *    you permission to link this library with independent modules to 
 *    produce an executable, regardless of the license terms of these 
 *    independent modules, and to copy and distribute the resulting 
 *    executable under terms of your choice, provided that you also meet, 
 *    for each linked independent module, the terms and conditions of the 
 *    license of that module.  An independent module is a module which 
 *    is not derived from or based on this library.  If you modify this 
 *    library, you may extend this exception to your version of the 
 *    library, but you are not obligated to do so.  If you do not wish 
 *    to do so, delete this exception statement from your version.
 *
 *    Project: Financas Pessoais
 *    Author : Igor Salles
 */
package org.fp.db;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.UnsupportedEncodingException;
import java.net.URL;

//Logging tools
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.fp.logger.Jornal;
//Text tool
import org.fp.util.CSVTokenizer;
//modelo
import org.fp.model.Bancos;
//banco de dados
import java.sql.Connection;
import java.sql.Statement;
import java.sql.PreparedStatement;
import org.fp.util.RemoveAcentos;
/**
 *
 * @author Igor Salles
 * @since May 19, 2011
 */
public class PopulateBancos {

    private static File arq = null;
    private static FileInputStream fis = null;
    private static final Logger jornal = Jornal.getInstance();
    private static ArrayList<Bancos> lista = new ArrayList<Bancos>();
    private static Bancos bco = null;
    private static RemoveAcentos ra;
    
    private static Connection conn = null;
    

    public PopulateBancos() {
    }

    private static void unloadCSV() {
        try{
        //abrir conexao com DB, e carregar na tabela de bancos;
        conn = Connections.getInstance();
        //verifica se o autocommit esta ativo se estiver desativa
        if(conn.getAutoCommit()){
            conn.setAutoCommit(false);
        }
        
        String inSql = "INSERT INTO tbBancos VALUES(?, ?, ?)";
        PreparedStatement pstmt = conn.prepareStatement(inSql);
        
        
        for (Bancos bancos : lista) {
            pstmt.setObject(1, bancos.getIdBanco());            
            pstmt.setObject(2, bancos.getNomeBanco());
            pstmt.setObject(3, bancos.getNomeReduzido());
            pstmt.addBatch();
            System.out.println(bancos.toString());
        }
        
        int updateCount[] = pstmt.executeBatch();
        
            for (int i=0; i<updateCount.length;i++){
                if(updateCount[i] >= 0){
                    //ok com numero de rows afetados
                   // System.out.println(""+updateCount[i]+" "+i);
                }else if(updateCount[i] == Statement.SUCCESS_NO_INFO){
                    //ok sem numero de rows afetados
                }else if(updateCount[i] == Statement.EXECUTE_FAILED){
                    //fail to execute
                }
            }

            conn.commit();
        }catch(Exception e){
            e.printStackTrace();            
        }
        
        
    }

    private static void loadCSV() {
        CSVTokenizer csvtzer;
        try {
            URL csvPath = ClassLoader.getSystemResource("org/fp/files/tbBancos.csv");
            arq = new File(csvPath.getPath());
            fis = new FileInputStream(arq);

            InputStreamReader reader = new InputStreamReader(fis, "iso-8859-1");
            BufferedReader bufferedReader = new BufferedReader(reader);

            String record = "";
            int counter;
            int auxID = 999;


            while ((record = bufferedReader.readLine()) != null) {
                bco = new Bancos();
                counter = 0;
                
                String tmp = "";
                String dumpTmp;
                csvtzer =
                        new CSVTokenizer(record, ";", CSVTokenizer.DOUBLE_QUATE);
                //System.out.println("" + csvtzer.countTokens());

                while (csvtzer.hasMoreTokens()) {
                    counter++;
                    
                    if (counter == 3) {
                        bco.setNomeReduzido(ra.remover(csvtzer.nextToken()));
                        jornal.info(bco.getNomeReduzido());
                    } else if (counter == 4) {
                        tmp = csvtzer.nextToken();
                        if (tmp.contains("-")) {
                            //nao existe numero de banco pra este cara entao
                            jornal.info("Using auxiliar ID for a non-existent id");
                            bco.setIdBanco(auxID);
                            auxID--;
                        } else {
                            bco.setIdBanco(Integer.parseInt(tmp));
                        }
                        jornal.info("" + bco.getIdBanco());
                    } else if (counter == 6) {
                        bco.setNomeBanco(ra.remover(csvtzer.nextToken()));
                        jornal.info(bco.getNomeBanco());
                    } else {
                        //preciso dumpar os tokens inutilizados
                        dumpTmp = csvtzer.nextToken();
                    }
                }
                lista.add(bco);
                bco.toString();
//                System.out.println("" + record);
//                System.out.println("" + csvPath);
            }
            jornal.info("Closing resources.");
            bufferedReader.close();
            reader.close();
            fis.close();
            
            System.out.println("------------------------------------------------------------------------");
            System.out.println("Listando os bancos registrados");
            System.out.println("------------------------------------------------------------------------");
            for (Bancos bancos : lista) {
                //System.out.println("" + bancos.getIdBanco() + " - " + bancos.getNomeReduzido() + " - " + bancos.getNomeBanco());
            }
            
//            System.out.println("------------------------------------------------------------------------");
//            System.out.println("Listando os bancos registrados");
//            System.out.println("------------------------------------------------------------------------");
//            System.out.println(lista);

        } catch (NullPointerException er) {
            jornal.log(Level.SEVERE,
                    "File name is wrong, or its not in the right location.", er);
            //System.exit(1);
        } catch (FileNotFoundException ex) {
            jornal.log(Level.SEVERE, "File not found.", ex);
        } catch (UnsupportedEncodingException e) {
            jornal.log(Level.SEVERE, "Text encoding not supported.", e);
        } catch (IOException exx) {
            jornal.log(Level.SEVERE, "Erro trying to read file.", exx);
        }


    }

    public static void main(String a[]) {
        //
        PopulateBancos populateBancos = new PopulateBancos();
        populateBancos.loadCSV();
        populateBancos.unloadCSV();
    }
}
